



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_tb_svcorderrawmaterials]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_tb_svcorderrawmaterials]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.v_tb_svcorderrawmaterials
AS
SELECT bid, bno, bdtlno, productid, cprice, bprice, bquantity, amount, unitid, quantity, price, 
      unit1qty, unit2qty, unit3qty, assqty, relationassqty, batchcode, productdate, validdate, 
      drawqty + ISNULL
          ((SELECT SUM(drawqty / rate)
          FROM tb_svcorderrawsub s
          WHERE s.bid = d.bid AND s.bno = d.bno AND s.bdtlno = d.bdtlno), 0) AS drawqty, 
      drawamt, wastedqty + ISNULL
          ((SELECT SUM(wastedqty / rate)
          FROM tb_svcorderrawsub s
          WHERE s.bid = d.bid AND s.bno = d.bno AND s.bdtlno = d.bdtlno), 0) 
      AS wastedqty, wastedamt, relationcount, relationbtype, relationbid, relationbno, 
      relationpid, relationqty, ioqty, explan, custom1, custom2, custom3, custom4, custom5, 
      custom6, custom7, custom8, custom9, custom10, custom11, custom12, custom13, 
      custom14, custom15, drawqty AS formerdrawqty, wastedqty AS formerwastedqty, 
      workprocid
FROM dbo.tb_svcorderrawmaterials d

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
